﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.Odbc;
using System.IO;
using System.Data.SqlClient;

namespace Btrieve_Retriever
{
    public partial class Form1 : Form
    {
        static int currentRow, rowCount, currentTable, tableCount;
        OdbcDataReader reader;
        Form3 form;

        public Form1()
        {
            InitializeComponent();
            populateList(); 
        }

        private void convertToSQL()
        {
            //Program.myConnection2.Open();
            tableCount = listBox1.Items.Count;

            String allTables = "";
            foreach (string tableName in listBox1.Items)
            {
                allTables += tableName + ",";
            }
            progressBar1.Visible = true;
            label1.Visible = true;
            backgroundWorker2.RunWorkerAsync(allTables);
        }


        public int DropTableIfExists(string tableName, SqlConnection sqlcon)
        {
            // Drop the table if it already exists.
            string sql = String.Format("IF OBJECT_ID('{0}') IS NOT NULL DROP TABLE {0}", tableName);
            SqlCommand cmd = new SqlCommand(sql, sqlcon);
            return cmd.ExecuteNonQuery();
        }

        public int CreateTable(string tableName, SqlConnection sqlcon, OdbcDataReader reader)
        {
            // Create the table.
            string sql = GenerateTableSQL(tableName, reader);
            SqlCommand cmd = new SqlCommand(sql, sqlcon);
            return cmd.ExecuteNonQuery();
        }
        public void PopulateTable(string tableName, SqlConnection sqlcon, OdbcDataReader reader)
        {
            currentRow = 0;
            SqlBulkCopy bulkCopy = new SqlBulkCopy(
               sqlcon,
               SqlBulkCopyOptions.TableLock |
               SqlBulkCopyOptions.KeepNulls |
               SqlBulkCopyOptions.KeepIdentity,
               null
               );

            bulkCopy.SqlRowsCopied += new SqlRowsCopiedEventHandler(OnSqlRowsCopied);
            bulkCopy.NotifyAfter = 1;
            bulkCopy.DestinationTableName = tableName;
            bulkCopy.BatchSize = 2000;
            Console.WriteLine("2");
            bulkCopy.WriteToServer(reader);
            Console.WriteLine("3");
           
        }

        private void backgroundWorker2_DoWork(object sender, DoWorkEventArgs e)
        {
            String tN = e.Argument.ToString();
            char[] charSeparators = new char[] { ',' };
            String[] tableNames = tN.Split(charSeparators, StringSplitOptions.RemoveEmptyEntries);
            tableCount = tableNames.Length;

           /* foreach (String tableName in tableNames)
            {
                currentTable++;
                String query2 = "SELECT COUNT(*) FROM " + tableName;
                OdbcCommand command2 = new OdbcCommand(query2, Program.myConnection);
                rowCount = (int)command2.ExecuteScalar();

                Console.WriteLine("Begin Copy: " + tableName);
                String query = "SELECT * FROM " + tableName;
                OdbcCommand command = new OdbcCommand(query, Program.myConnection);
                reader = command.ExecuteReader(CommandBehavior.SequentialAccess);
                Console.WriteLine("COPYING: " + tableName);
                DropTableIfExists(tableName, Program.myConnection2);
                CreateTable(tableName, Program.myConnection2, reader);
                PopulateTable(tableName, Program.myConnection2, reader);
                Console.WriteLine("COPIED: " + tableName);
            }*/

            string tableName = "TTSTDAT";
            currentTable++;
            String query2 = "SELECT COUNT(*) FROM TTSTDAT";
            OdbcCommand command2 = new OdbcCommand(query2, Program.myConnection);
            rowCount = (int)command2.ExecuteScalar();

            Console.WriteLine("Begin Copy: " + tableName);
            String query = "SELECT * FROM " + tableName;
            OdbcCommand command = new OdbcCommand(query, Program.myConnection);
            reader = command.ExecuteReader(CommandBehavior.SequentialAccess);
            Console.WriteLine("COPYING: " + tableName);
            DropTableIfExists(tableName, Program.myConnection2);
            CreateTable(tableName, Program.myConnection2, reader);
            PopulateTable(tableName, Program.myConnection2, reader);
            Console.WriteLine("COPIED: " + tableName);
            

            
        }

      public void OnSqlRowsCopied(Object sender, SqlRowsCopiedEventArgs e)
        {
            Console.WriteLine("1");
            currentRow++;
            double perc = ((double)currentRow / rowCount) * 100.0;
            int percentageComplete = Convert.ToInt32(perc);

            backgroundWorker2.ReportProgress(percentageComplete, e.RowsCopied);
          
            
        }
      private void backgroundWorker2_ProgressChanged(object sender, ProgressChangedEventArgs e)
      {
          progressBar1.Value = e.ProgressPercentage;
          label1.Text = "Converted " + currentRow + " out of " + rowCount + " records of " + currentTable + " of " + tableCount + " tables";
          Console.WriteLine("Copied {0} Lines so far:", e.UserState);
      }

    private void backgroundWorker2_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
       {
            label1.Text = "DONE";
            Console.WriteLine("DD§");
            backgroundWorker1.Dispose();
            progressBar1.Visible = false;
       }

        public static string GenerateTableSQL(string tableName, OdbcDataReader reader)
        {
            string sql = "CREATE TABLE [" + tableName + "] (\n";

            int fCount = reader.FieldCount;
            for (int i = 0; i < fCount; i++)
            {
                sql += "\t[" + reader.GetName(i) + "] " + SQLGetType(reader.GetFieldType(i)) + ",\n";
            }

            sql = sql.TrimEnd(new char[] { ',', '\n' }) + "\n)";

            return sql;
        }

        public static string SQLGetType(Type type)
        {
            switch (type.ToString())
            {
                case "System.String":
                    return "VARCHAR(8000)";

                case "System.Decimal":
                case "System.Double":
                case "System.Single":
                    return "REAL";

                case "System.Int64":
                    return "BIGINT";

                case "System.Int16":
                case "System.Int32":
                    return "INT";

                case "System.DateTime":
                    return "DATETIME";

                case "System.Boolean":
                    return "BIT";

                case "System.Byte":
                    return "TINYINT";

                case "System.Guid":
                    return "UNIQUEIDENTIFIER";

                default:
                    throw new Exception(type.ToString() + " not implemented.");
            }
        }



        private void populateList()
        {
       
            DataTable columns = Program.myConnection.GetSchema("Columns");
            foreach (DataRow row in columns.Rows)
            {
                String tableName = row["TABLE_NAME"].ToString();

                if (listBox1.FindString(tableName) == System.Windows.Forms.ListBox.NoMatches && tableName[1] != '$')
                {
                    listBox1.Items.Add(tableName);
                    listBox2.Items.Add(tableName);
                }
            }
           
        }

 

        private void listBox1_KeyPress(object sender, KeyPressEventArgs e)
        {
            string selected = listBox1.SelectedItem.ToString();
            if (e.KeyChar == 13) {
                Console.WriteLine("SELECTED: " + selected);
                //this.Hide();
                //Form2 form = new Form2(selected);
                var newThread = new System.Threading.Thread(delegate()
                {
                    Form3 form = new Form3(selected);
                    form.ShowDialog();
                });
                newThread.SetApartmentState(System.Threading.ApartmentState.STA);
                newThread.Start();

                newThread.Join();
            }
        }

        private void listBox1_DoubleClick(object sender, EventArgs e)
        {
            string selected = listBox1.SelectedItem.ToString();
            Console.WriteLine("SELECTED: " + selected);
            //this.Hide();
            //Form2 form = new Form2(selected);

            var newThread = new System.Threading.Thread(delegate()
            {
                Form3 form = new Form3(selected);
                form.ShowDialog();
            });
            newThread.SetApartmentState(System.Threading.ApartmentState.STA);
            newThread.Start();
        }

        private void listBox2_KeyPress(object sender, KeyPressEventArgs e)
        {

            if (e.KeyChar == 13)
            {

                string selected = "";
                foreach (String str in listBox2.SelectedItems)
                {
                    selected += str + ",";
                }
               
            }
        }

        private void listBox2_DoubleClick(object sender, EventArgs e)
        {

            string selected = "";
            foreach (String str in listBox2.SelectedItems)
            {
                selected += str + ",";
            }
            backgroundWorker1.RunWorkerAsync(selected);
            progressBar1.Visible = true;
        }

        private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
        {
            currentRow = 0; rowCount = 0; currentTable = 0;
            Console.WriteLine("CONVERSION STARTED");
            String tN = e.Argument.ToString();
            char[] charSeparators = new char[] { ',' };
            String[] tableNames = tN.Split(charSeparators,StringSplitOptions.RemoveEmptyEntries);
            tableCount = tableNames.Length;

            foreach (String x in tableNames)
            {
                Console.WriteLine("Tables: " + x);
            }
            foreach (String tNstr in tableNames)
            {
                currentTable++;

                String tableName = tNstr;
                String query2 = "SELECT COUNT(*) FROM " + tableName;
                OdbcCommand command2 = new OdbcCommand(query2, Program.myConnection);
                rowCount = (int)command2.ExecuteScalar();

                String query = "SELECT * FROM " + tableName;
                OdbcCommand command = new OdbcCommand(query, Program.myConnection);
                OdbcDataReader reader = command.ExecuteReader(CommandBehavior.SequentialAccess);


                DataTable table = new DataTable();
                String path = "c:\\NieseData2\\" + tableName + ".csv";
                StreamWriter sw = new StreamWriter(path, false);

                int columnCount = reader.FieldCount;
                for (int i = 0; i < columnCount; i++)
                {
                    sw.Write(reader.GetName(i));
                    if (i < (columnCount - 1))
                    {
                        sw.Write(",");
                    }
                }
                sw.Write(sw.NewLine);
                currentRow = 0;
                while (reader.Read())
                {
                    for (int i = 0; i < columnCount; i++)
                    {
                        try
                        {
                            sw.Write(reader.GetString(i) + ",");
                        }
                        catch (Exception ex)
                        { 
                        }
                    }

                    double perc = ((double)currentRow / rowCount) * 100.0;
                    int percentageComplete = Convert.ToInt32(perc);
                    backgroundWorker1.ReportProgress(percentageComplete);
                    currentRow++;
                    sw.Write(sw.NewLine);
                }
                sw.Close();
            }

      /*      String query = "SELECT * FROM " + tableName;
            OdbcCommand command = new OdbcCommand(query, Program.myConnection);
            OdbcDataAdapter adapter = new OdbcDataAdapter(query, Program.myConnection);
            adapter.Fill(table);
            String path = "c:\\NieseData\\" + tableName + ".csv";
            StreamWriter sw = new StreamWriter(path, false);
            
            Console.WriteLine("Filled table for conversion");

            int columnCount = table.Columns.Count;
            for (int i = 0; i < columnCount; i++)
            {
                sw.Write(table.Columns[i]);
                if (i < (columnCount - 1))
                {
                    sw.Write(",");
                }
            }
            sw.Write(sw.NewLine);
            
            int rowCount = table.Rows.Count;
            for (int j = 0; j < rowCount; j++)
            {
                for (int i = 0; i < columnCount; i++)
                {

                    if (!Convert.IsDBNull(table.Rows[j][i]))
                    {
                        sw.Write(table.Rows[j][i].ToString());
                    }
                    if (i < (columnCount - 1))
                    {
                        sw.Write(",");
                    }
                }
                double perc = ((double)j / rowCount) * 100.0;
                int percentageComplete = Convert.ToInt32(perc);
                Console.WriteLine("PROGRESS: " + perc);
                backgroundWorker1.ReportProgress(percentageComplete);
                sw.Write(sw.NewLine);
            }
            sw.Close();*/
        }

        private void backgroundWorker1_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            Console.WriteLine("COMPLETED");
            progressBar1.Visible = false;
        }

        private void backgroundWorker1_ProgressChanged(object sender, ProgressChangedEventArgs e)
        {
            progressBar1.Value = e.ProgressPercentage;
            label1.Text = "Converted " + currentRow + " out of " + rowCount + " records of " + currentTable + " of " + tableCount + " tables";
        }

        private void button1_Click(object sender, EventArgs e)
        {

            string selected = "";
            foreach (String str in listBox2.SelectedItems)
            {
                selected += str + ",";
            }
            backgroundWorker1.RunWorkerAsync(selected);
            progressBar1.Visible = true;
        }

        private void button2_Click(object sender, EventArgs e)
        {
            convertToSQL();
        }

        private void label2_Click(object sender, EventArgs e)
        {

        }

        private void button3_Click(object sender, EventArgs e)
        {
            OrderDescrip orderDescrip = new OrderDescrip(textBox1.Text);
            orderDescrip.ShowDialog();
        }

        

        /*private void createNewTable(string tableName)
{
    Program.myConnection2.Open();
    SqlCommand sCommand1 = new SqlCommand("IF OBJECT_ID('" + tableName + "', 'U') " + "IS NOT NULL BEGIN DROP TABLE " + tableName + " END" , Program.myConnection2);
    SqlCommand sCommand = new SqlCommand("CREATE TABLE " + tableName + " (aufnr1 varchar(Max));",Program.myConnection2);
    sCommand1.ExecuteNonQuery();
    sCommand.ExecuteNonQuery();
}*/

        /*private void fillTable(String tableName)
        {
           // Console.WriteLine("FILLING TABLE: " + tableName);
            //Program.myConnection.Open();
            Program.myConnection2.Open();
            String query = "SELECT * FROM " + tableName;
            OdbcCommand command = new OdbcCommand(query, Program.myConnection);
             reader = command.ExecuteReader(CommandBehavior.SequentialAccess);


             SqlBulkCopy bulkCopy = new SqlBulkCopy(
                Program.myConnection2,
                SqlBulkCopyOptions.TableLock |
                SqlBulkCopyOptions.KeepNulls |
                SqlBulkCopyOptions.KeepIdentity,
                null
                );
 
            bulkCopy.DestinationTableName = tableName;
 
      
            bulkCopy.BatchSize = 500;
 
            bulkCopy.WriteToServer(reader);
            Program.myConnection.Close();
            Program.myConnection2.Close();
        }
        */

        /*private void fillTable(string tableName)
        {
            Console.WriteLine("FILLING TABLE: " + tableName);
            String query = "SELECT * FROM " + tableName;
            OdbcCommand command = new OdbcCommand(query, Program.myConnection);
            OdbcDataReader reader = command.ExecuteReader(CommandBehavior.SequentialAccess);

            int columnCount = reader.FieldCount;
            for (int i = 0; i < columnCount; i++)
            {
                SqlCommand sCommand = new SqlCommand("ALTER TABLE " + tableName + " ADD " + reader.GetName(i) + " varchar(MAX)", Program.myConnection2);
                sCommand.ExecuteNonQuery();
            }

            string row="";
            while (!reader.IsClosed)
            {
                try
                {
                    row = "";
                    String []x = new String[columnCount];
                    reader.Read();
                    for (int i = 0; i < columnCount; i++)
                    {
                        if (reader != null)
                        {
                                if (reader.GetString(i).Contains('\''))
                                {
                                    Console.WriteLine("REPLACED QUOT");
                                    String s = reader.GetString(i).Replace("\'", "A");
                                    Console.WriteLine(s);
                                    row += s;
                                }
                                else
                                {
                                    row += "\'" + reader.GetString(i).Trim() + "\',";
                                }
                                
                           // Console.WriteLine("FILLER: " + reader.GetString(i));
                        }
                        //Console.WriteLine(row);
                    }
                    //Console.WriteLine();
                    row = row.Substring(0, row.Length - 1);
                    SqlCommand insertCommand = new SqlCommand("INSERT INTO " + tableName  + " VALUES(\'1\'," + row + ")", Program.myConnection2);
                    insertCommand.ExecuteNonQuery();
                }
                catch (SqlException exp)
                {
                    Console.WriteLine(exp.StackTrace);
                    Console.WriteLine(row);
                   // this.Close();
                }
            }
            Program.myConnection2.Close();
        }*/
    }
}